Introduction

Introduction

Collecting the Data

Import pandas and numpy

import pandas as pd
import numpy as np

Let’s take a look at the files in our input directory, using the convenient shell commands in ipython.

!ls data

311-service-requests.csv
All-Web-Site-Data-Audience-Overview.xlsx
Aussie_Wines_Plotting.csv
March-2017-forecast-article.xlsx
Traffic_20170306-20170519.xlsx
Tweets.csv
airbnb_session_data.txt
customer-status.xlsx
debtors.csv
excel_sample.xlsx
general-ledger-sample.xlsx
mn-budget-detail-2014.csv
pnl.xlsx
population.xlsx
sales-estimate.xlsx
sales-feb-2014.xlsx
sales-jan-2014.xlsx
sales-mar-2014.xlsx
sales_data_types.csv
sales_transactions.xlsx
salesfunnel.xlsx
sample-sales-reps.xlsx
sample-sales-tax.csv
sample-salesv3.xlsx
tb_import.xlsx
~$general-ledger-sample.xlsx

There are a lot of files, but we only want to look at the sales .xlsx files.

!ls data/sales-*-2014.xlsx

data/sales-feb-2014.xlsx data/sales-jan-2014.xlsx data/sales-mar-2014.xlsx

Use the python glob module to easily list out the files we need

import glob

glob.glob("data/sales-*-2014.xlsx")

['data/sales-feb-2014.xlsx',
 'data/sales-jan-2014.xlsx',
 'data/sales-mar-2014.xlsx']

This gives us what we need, let’s import each of our files and combine them into one file.

Panda’s concat and append can do this for us. I’m going to use append in this example.

The code snippet below will initialize a blank DataFrame then append all of the individual files into the all_data DataFrame.

all_data = pd.DataFrame()
for f in glob.glob("data/sales-*-2014.xlsx"):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)

Now we have all the data in our all_data DataFrame. You can use describe to look at it and make sure you data looks good.

all_data.shape

(384, 7)

all_data.describe()

Alot of this data may not make much sense for this data set but I’m most interested in the count row to make sure the number of data elements makes sense.

all_data.head()

It is not critical in this example but the best practice is to convert the date column to a date time object.

all_data['date'] = pd.to_datetime(all_data['date'])

Combining Data

Now that we have all of the data into one DataFrame, we can do any manipulations the DataFrame supports. In this case, the next thing we want to do is read in another file that contains the customer status by account. You can think of this as a company’s customer segmentation strategy or some other mechanism for identifying their customers.

First, we read in the data.

status = pd.read_excel("data/customer-status.xlsx")
status

We want to merge this data with our concatenated data set of sales. We use panda’s merge function and tell it to do a left join which is similar to Excel’s vlookup function.

all_data_st = pd.merge(all_data, status, how='left')
all_data_st.head()

This looks pretty good but let’s look at a specific account.

all_data_st[all_data_st["account number"]==737550].head()

This account number was not in our status file, so we have a bunch of NaN’s. We can decide how we want to handle this situation. For this specific case, let’s label all missing accounts as bronze. Use the fillna function to easily accomplish this on the status column.

all_data_st['status'].fillna('bronze',inplace=True)
all_data_st.head()

Check the data just to make sure we’re all good.

all_data_st[all_data_st["account number"]==737550].head()

Now we have all of the data along with the status column filled in. We can do our normal data manipulations using the full suite of pandas capability.

Using Categories

One of the relatively new functions in pandas is support for categorical data. From the pandas, documentation -

“Categoricals are a pandas data type, which correspond to categorical variables in statistics: a variable, which can take on only a limited, and usually fixed, number of possible values (categories; levels in R). Examples are gender, social class, blood types, country affiliations, observation time or ratings via Likert scales.”

For our purposes, the status field is a good candidate for a category type.

You must make sure you have a recent version of pandas installed for this example to work.

pd.__version__

'0.22.0'

First, we typecast it to a category using astype.

all_data_st["status"] = all_data_st["status"].astype("category")

This doesn’t immediately appear to change anything yet.

all_data_st.head()

Buy you can see that it is a new data type.

all_data_st.dtypes

account number             int64
name                      object
sku                       object
quantity                   int64
unit price               float64
ext price                float64
date              datetime64[ns]
status                  category
dtype: object

Categories get more interesting when you assign order to the categories. Right now, if we call sort on the column, it will sort alphabetically.

all_data_st.sort_values(by=["status"]).head()

We use set_categories to tell it the order we want to use for this category object. In this case, we use the Olympic medal ordering.

 all_data_st["status"].cat.set_categories([ "gold","silver","bronze"],inplace=True)

Now, we can sort it so that gold shows on top.

all_data_st.sort_values(by=["status"]).head()

all_data_st["status"].describe()

count        384
unique         3
top       bronze
freq         172
Name: status, dtype: object

For instance, if you want to take a quick look at how your top tier customers are performaing compared to the bottom. Use groupby to give us the average of the values.

all_data_st.groupby(["status"])["quantity","unit price","ext price"].mean()

Of course, you can run multiple aggregation functions on the data to get really useful information

all_data_st.groupby(["status"])["quantity","unit price","ext price"].agg([np.sum,np.mean, np.std])

So, what does this tell you? Well, the data is completely random but my first observation is that we sell more units to our bronze customers than gold. Even when you look at the total dollar value associated with bronze vs. gold, it looks backwards.

Maybe we should look at how many bronze customers we have and see what is going on.

What I plan to do is filter out the unique accounts and see how many gold, silver and bronze customers there are.

I’m purposely stringing a lot of commands together which is not necessarily best practice but does show how powerful pandas can be. Feel free to review my previous articles and play with this command yourself to understand what all these commands mean.

all_data_st.drop_duplicates(subset=["account number","name"]).iloc[:,[0,1,7]].groupby(["status"])["name"].count()

status
gold      4
silver    7
bronze    9
Name: name, dtype: int64

Ok. This makes a little more sense. We see that we have 9 bronze customers and only 4 customers. That is probably why the volumes are so skewed towards our bronze customers.

all_data_st.head(4)

We will start over and import a clean file

df = pd.read_excel("data/sample-sales-reps.xlsx")

## Set default commision of 3%
df["commission"] = .03
df.head()

Since shirts are high margin, adjust all products in the shirt categort with a commission rate of 5%

df.loc[df["category"] == "Shirt", ["commission"]] = .05
df.head()

Since there is a special program for selling 10 or more belts in a transaction, you get 7% commission!

df.loc[(df["category"] == "Belt") & (df["quantity"] >= 10), ["commission"]] = .04
df.head()

Finally, some transactions can get a bonus and a commission increase.

df["bonus"] = 0
df.loc[(df["category"] == "Shoes") & (df["ext price"] >= 1000 ), ["bonus", "commission"]] = 250, 0.045

Calculate the compensation at the line item level

df["comp"] = df["commission"] * df["ext price"] + df["bonus"]
df.head()

Calculate the commissions by sales rep

df.groupby(["sales rep"])["comp"].sum().round(2)

sales rep
Ansley Cummings       2699.69
Beth Skiles           3664.16
Esequiel Schinner    12841.28
Loring Predovic      13115.42
Shannen Hudson        6541.78
Teagan O'Keefe       10931.30
Trish Deckow          7641.91
Name: comp, dtype: float64

df["date"] = pd.to_datetime(df['date'])

df["month"] = df["date"].dt.month

Now what about on a monthly basis

df.head()

df.groupby(["month","sales rep"])["comp"].sum().round(2)

month  sales rep        
1      Ansley Cummings       157.10
       Beth Skiles            78.09
       Esequiel Schinner    1481.78
       Loring Predovic       655.96
       Shannen Hudson        319.13
       Teagan O'Keefe        732.10
       Trish Deckow          305.58
2      Ansley Cummings       347.06
       Beth Skiles          1262.36
       Esequiel Schinner     741.87
       Loring Predovic      1794.22
       Shannen Hudson        524.58
       Teagan O'Keefe        893.54
       Trish Deckow          430.81
3      Ansley Cummings       362.13
       Beth Skiles           439.53
       Esequiel Schinner    1323.87
       Loring Predovic      1183.59
       Shannen Hudson        474.90
       Teagan O'Keefe       1064.76
       Trish Deckow          796.12
4      Ansley Cummings       123.27
       Beth Skiles           121.12
       Esequiel Schinner    1478.78
       Loring Predovic       907.41
       Shannen Hudson        514.77
       Teagan O'Keefe        593.64
       Trish Deckow          347.04
5      Ansley Cummings       101.29
       Beth Skiles           347.73
                             ...   
8      Teagan O'Keefe        477.33
       Trish Deckow         1493.39
9      Ansley Cummings       109.70
       Beth Skiles           145.43
       Esequiel Schinner     178.20
       Loring Predovic       978.46
       Shannen Hudson        765.28
       Teagan O'Keefe       1086.80
       Trish Deckow          435.42
10     Ansley Cummings       510.49
       Beth Skiles           204.40
       Esequiel Schinner     994.76
       Loring Predovic      1954.61
       Shannen Hudson        990.48
       Teagan O'Keefe       1111.47
       Trish Deckow          625.12
11     Ansley Cummings        80.41
       Beth Skiles           464.13
       Esequiel Schinner     928.05
       Loring Predovic      1253.52
       Shannen Hudson        567.31
       Teagan O'Keefe        554.03
       Trish Deckow          281.69
12     Ansley Cummings       288.82
       Beth Skiles           131.40
       Esequiel Schinner    1235.69
       Loring Predovic       660.44
       Shannen Hudson        421.24
       Teagan O'Keefe        421.16
       Trish Deckow          848.57
Name: comp, Length: 84, dtype: float64

You can do this for the entire numerical dataframe, withou creating a month variable like follows

df.set_index('date').groupby('sales rep').resample("M").sum().head(20)

What if you are only interested in Mondays

df.set_index('date').groupby('sales rep').resample("W-Mon").sum().head(20)

for a specific month use: df.groupby([‘name’, ‘sku’, pd.Grouper(key=’date’, freq=’A-DEC’)])[‘ext price’].sum()

Here is an aggregation function that is sometimes usefull

df.agg({'ext price': ['sum', 'mean'], 'quantity': ['sum', 'mean'], 'unit price': ['mean']})

You can create custom functions

get_max = lambda x: x.value_counts(dropna=False).index[0]
get_max.__name__ = "most frequent" # required for row label

df.agg({'ext price': ['sum', 'mean'], 'quantity': ['sum', 'mean'], 'unit price': ['mean'], 'sku': [get_max]})

It is nice to have an ordered dictionary

import collections
f = collections.OrderedDict([('ext price', ['sum', 'mean']), ('quantity', ['sum', 'mean']), ('sku', [get_max])])
df.agg(f)

You can of course use any of the extensions available like below to further analysis and filtering.

import qgrid
from IPython.display import display

qgrid_widget = qgrid.show_grid(df, show_toolbar=True)

## Not sure why this is not working - giving it a skip for now

qgrid_widget

QgridWidget(grid_options={'fullWidthRows': True, 'syncColumnCellResize': True, 'forceFitColumns': True, 'defau…

qgrid_widget.get_changed_df()

df.head()

df.groupby("category").agg({"quantity":["count","size"],"unit price":["sum"],"ext price":['mean']})

What is nice about the pivot table, is that you have three dataframe parameters, index, columns, and values to adjust, wheras the groupby function only use index and values.

df.pivot_table(index=["month","sales rep"],columns=["category"], values=["bonus","comp"],aggfunc=[np.sum, np.size],fill_value="No Sale").head(12)

In the privot table below, only certain calculations are applied to certain columns. It is more selective.

# this is whithout brackets, so the type of calculation is not displayed
#df.pivot_table(index=["month","sales rep"],columns=["category"], values=["comp","bonus"],aggfunc={"comp":np.sum, "bonus":np.size},fill_value="No Sale").head(12)

df.pivot_table(index=["month","sales rep"],aggfunc={"comp":[np.sum], "bonus":[np.size]},columns=["category"], values=["comp","bonus"],fill_value="No Sale").head(12)

df_pivot = df.pivot_table(index=["month","sales rep"],aggfunc={"comp":[np.sum], "bonus":[np.size]},columns=["category"], values=["comp","bonus"],fill_value="No Sale")

You can now if you feel the need to, do some querying

df_pivot.query("month == [1]")

This is another way to do it, I find it more reliable

df_pivot[df_pivot.index.get_level_values(0).isin([1])]

df_pivot[df_pivot.index.get_level_values(1).isin(["Ansley Cummings"])]

df.head()